def save_list_entry(self, list_entry: ListEntry):
"""Listeneintrag aktualisieren
:param list_entry: Listeneintrag-Objekt
:return: Aktualisiertes Listeneintrag-Objekt
"""
shopping_list = self.get_shopping_list_by_id(
list_entry.get_shopping_list())
self.save_shopping_list(shopping_list)
list_entry.set_last_updated(datetime.now())
with ListEntryMapper() as mapper:
return mapper.update(list_entry)
def post(self, shopping_list_id):
""" Wir verwenden Namen, amount, article, Unit, Purchasing_user,
retailer und shoppinglist des Proposals für die Erzeugung
eines Gruppen-Objekts. Das serverseitig erzeugte
Objekt ist das maßgebliche und
wird auch dem Client zurückgegeben. """
adm = Administration()
sl = adm.get_shopping_list_by_id(shopping_list_id)
proposal = ListEntry.from_dict(api.payload)
if sl is not None and proposal is not None:
result = adm.create_list_entry(proposal.get_name(),
proposal.get_amount(),
proposal.get_article(),
proposal.get_unit(),
proposal.get_purchasing_user(),
proposal.get_retailer(),
proposal.get_shopping_list(),
proposal.is_standardarticle())
if proposal.is_standardarticle():
shopping_list = adm.get_shopping_list_by_id(
proposal.get_shopping_list())
group = adm.get_group_by_id(shopping_list.get_group())
adm.add_standardarticle_to_group(result, group)
return result, 200
else:
return 'ShoppingList unknown or payload not valid', 500
def find_standardarticles_by_group_id(self, group_id):
"""Auslesen von als Standard-Artikel markierten
Listeneinträgen durch Fremdschlüssel (group_id)
geg. Gruppe
:param group_id:
:return Eine Sammlung mit Listeneintrag-Objekten, mit standardarticle
= 1.
"""
cursor = self._connection.cursor()
command = "SELECT standard_article_group_relations.list_entry_id," \
" holma.list_entry.name, holma.list_entry.creation_date, " \
"holma.list_entry.purchasing_user, " \
"holma.list_entry.amount, holma.list_entry.article, " \
"holma.list_entry.unit, holma.list_entry.retailer, " \
"holma.list_entry.standardarticle, " \
"holma.list_entry.checked, holma.list_entry.shopping_list," \
" holma.list_entry.last_updated, " \
"holma.list_entry.checked_ts FROM " \
"standard_article_group_relations INNER JOIN list_entry ON" \
" standard_article_group_relations.list_entry_id=" \
"holma.list_entry.list_entry_id WHERE " \
"standard_article_group_relations.group_id = {}".format(
group_id)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def find_by_purchasing_user(self, user_id, archived_shopping_lists):
"""Auslesen von Listeneinträgen durch Fremdschlüssel (user_id)
geg. Einkaufender User
:param user_id:
:return Eine Sammlung mit Listeneintrag-Objekten.
"""
cursor = self._connection.cursor()
if archived_shopping_lists == []:
command = "SELECT * FROM holma.list_entry " \
"WHERE purchasing_user={}".format(user_id)
else:
shopping_list_ids = ", ".join([
str(shopping_list.get_id())
for shopping_list in archived_shopping_lists
])
command = "SELECT * FROM holma.list_entry " \
"WHERE purchasing_user={} " \
"AND shopping_list " \
"NOT IN ({})".format(user_id, shopping_list_ids)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def add_standardarticle_to_group(self, list_entry: ListEntry,
group: Group):
"""Standardartikel zur Gruppe hinzufügen
:param list_entry: Listeneintrag-Objekt
:param group: Gruppen-Objekt
:return:
"""
with ListEntryMapper() as mapper:
list_entry.set_id(0)
list_entry.set_standardarticle(True)
list_entry.set_purchasing_user(None)
list_entry.set_shopping_list(None)
list_entry.set_checked(False)
standardarticle = mapper.insert(list_entry)
mapper.insert_standardarticle(standardarticle, group)
def put(self, list_entry_id):
"""Update eines bestimmten Listeneintrag-Objekts."""
adm = Administration()
le = ListEntry.from_dict(api.payload)
if le is not None:
le.set_id(list_entry_id)
adm.save_list_entry(le)
return '', 200
else:
return '', 500
def delete_list_entry(self, list_entry: ListEntry):
"""Listeneintrag löschen
:param list_entry: Listeneintrag-Objekt
:return:
"""
shopping_list = self.get_shopping_list_by_id(
list_entry.get_shopping_list())
self.save_shopping_list(shopping_list)
with ListEntryMapper() as mapper:
mapper.delete(list_entry)
def find_all(self):
"""Auslesen aller vorhandenen Listeneinträge
:return Listeneitrag-Objekt, das der übergebenen ID entspricht oder None
wenn DB-Tupel nicht vorhanden ist.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry"
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def find_by_name(self, name):
"""Auslesen von Listeneinträgen durch Name
:param name:
:return Eine Sammlung mit Listeneintrag-Objekten.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry WHERE name LIKE '{}' " \
"ORDER BY name".format(name)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def find_by_retailer(self, retailer_id):
"""Auslesen von Listeneinträgen durch Fremdschlüssel (retailer_id)
geg. Einzelhändler
:param retailer_id:
:return Eine Sammlung mit Listeneintrag-Objekten.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry " \
"WHERE retailer={}".format(retailer_id)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def find_list_entries_by_shopping_list_id(self, shopping_list):
"""Auslesen von Listeneinträgen durch Fremdschlüssel (shopping_list_id)
geg. Shoppingliste
:param shopping_list:
:return Eine Sammlung mit Listeneintrag-Objekten.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry " \
"WHERE shopping_list={}".format(shopping_list.get_id())
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def find_list_entries_by_article(self, article_id):
"""Auslesen von Listeneinträgen durch Fremdschlüssel (article_id)
geg. Artikel
:param article_id:
:return Eine Sammlung mit Listeneintrag-Objekten.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry " \
"WHERE article={}".format(article_id)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def find_by_id(self, list_entry_id):
"""Eindeutiges Auslesen eines Listeneintrags durch ID
:return Listeneintrag-Objekt, das der übergebenen ID entspricht oder None
wenn DB-Tupel nicht vorhanden ist.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry " \
"WHERE list_entry_id={}".format(list_entry_id)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
if len(result) == 0:
return None
return result[0]
def find_list_entries_in_time_periode(self, from_date, to_date):
"""Auslesen von Listeneinträgen in einer bestimmten Zeitperiode
:param from_date:
:param to_date:
:return Eine Sammlung mit Listeneintrag-Objekten, deren last_updated
-Wert innerhalb der angegebenen Zeitperiode liegt.
"""
cursor = self._connection.cursor()
command = "SELECT * FROM holma.list_entry WHERE last_updated " \
"BETWEEN '{}' AND '{}'".format(from_date, to_date)
cursor.execute(command)
tuples = cursor.fetchall()
result = ListEntry.from_tuples(tuples)
self._connection.commit()
cursor.close()
return result
def create_list_entry(self, name: str, amount, article_id: int, unit: str,
purchasing_user_id: int, retailer_id: int,
shopping_list_id: int, is_standardarticle: bool):
"""Listeneintrag erstellen
:param name: Name
:param amount: Anzahl
:param article_id: ID des Artikels
:param unit: Einheit
:param purchasing_user_id: ID des Einkäufers
:param retailer_id: ID des Retailers
:param shopping_list_id: ID der Einkaufsliste
:param is_standardarticle: Information, ob es ein
Standardartikel ist
:return: Listeneintrag-Objekt
"""
shopping_list = self.get_shopping_list_by_id(shopping_list_id)
self.save_shopping_list(shopping_list)
list_entry = ListEntry()
list_entry.set_id(0),
list_entry.set_name(name),
list_entry.set_purchasing_user(purchasing_user_id),
list_entry.set_amount(amount),
list_entry.set_article(article_id),
list_entry.set_unit(unit),
list_entry.set_retailer(retailer_id),
list_entry.set_shopping_list(shopping_list_id)
list_entry.set_standardarticle(is_standardarticle)
with ListEntryMapper() as mapper:
return mapper.insert(list_entry)