def get_all_items():
database = db.get_db()
cur = database.execute("""SELECT * FROM item;""")
rows = cur.fetchall()
items = {}
idict = {'itemlist': [dict(row) for row in rows]}
return idict
def get_items_for_purchase_view(purchase_id):
database = db.get_db()
cur = database.execute("""SELECT s.name AS store, p.timestamp,
i.name, i.quantity, i.price, c.name AS categoryName
FROM purchase p, store s, item i, category c
WHERE p.id = %d AND p.storeid = s.id
AND i.purchaseid = p.id AND i.categoryid = c.id;"""
% purchase_id)
# cur = database.execute("""SELECT s.name AS store, p.timestamp, SUM(i.price) AS priceSum,
# i.name, i.quantity, i.price, c.name AS categoryName
# FROM purchase p, store s, item i, category c
# WHERE p.id = %d AND p.storeid = s.id
# AND i.purchaseid = p.id AND i.categoryid = c.id;""" % purchase_id)
rows = cur.fetchall()
items = [dict(row) for row in rows]
if len(items) < 1:
abort(500)
store = items[0]['store']
date_time = datetime.datetime.fromtimestamp(
items[0]['timestamp']).strftime('%d.%m.%Y %H:%M')
psum = helper.to_string_price(sum([i['price'] for i in items]))
for item in items:
item['price'] = helper.to_string_price(item['price'])
return {'store': store, 'datetime': date_time, 'sum': psum, 'items': items}
def get_items_for_purchase_view(purchase_id):
database = db.get_db()
cur = database.execute("""SELECT s.name AS store, p.timestamp,
i.name, i.quantity, i.price, c.name AS categoryName
FROM purchase p, store s, item i, category c
WHERE p.id = %d AND p.storeid = s.id
AND i.purchaseid = p.id AND i.categoryid = c.id;""" % purchase_id)
# cur = database.execute("""SELECT s.name AS store, p.timestamp, SUM(i.price) AS priceSum,
# i.name, i.quantity, i.price, c.name AS categoryName
# FROM purchase p, store s, item i, category c
# WHERE p.id = %d AND p.storeid = s.id
# AND i.purchaseid = p.id AND i.categoryid = c.id;""" % purchase_id)
rows = cur.fetchall()
items = [dict(row) for row in rows]
if len(items) < 1:
abort(500)
store = items[0]['store']
date_time = datetime.datetime.fromtimestamp(items[0]['timestamp']).strftime('%d.%m.%Y %H:%M')
psum = helper.to_string_price(sum([i['price'] for i in items]))
for item in items:
item['price'] = helper.to_string_price(item['price'])
return {'store': store, 'datetime': date_time, 'sum': psum, 'items': items}
def get_all_items():
database = db.get_db()
cur = database.execute("""SELECT * FROM item;""")
rows = cur.fetchall()
items = {}
idict = {'itemlist': [ dict(row) for row in rows ]}
return idict
def get_purchases_by_month():
database = db.get_db()
cur = database.execute("""SELECT p.id, p.timestamp, s.name AS storeName, SUM(i.price) AS priceSum
FROM purchase p, store s, item i
WHERE p.storeid = s.id AND i.purchaseid = p.id
GROUP BY i.purchaseid
ORDER BY p.timestamp DESC;""")
rows = cur.fetchall()
purchases = []
#pdict = {'purchaselist': [ dict(row) for row in rows ]}
for row in rows:
purchase_dict = dict(row)
purchase_dict['time'] = datetime.datetime.fromtimestamp(purchase_dict['timestamp']).strftime('%d.%m.%Y %H:%M')
purchase_dict['yearmonth'] = datetime.datetime.fromtimestamp(purchase_dict['timestamp']).strftime('%Y-%m')
purchase_dict['sum'] = helper.to_string_price(purchase_dict['priceSum'])
del purchase_dict['timestamp']
purchases.append(purchase_dict)
pmap = {}
for p in purchases:
yearmonth = p['yearmonth']
if not yearmonth in pmap:
pmap[yearmonth] = []
pmap[yearmonth].append(p)
rlist = []
for yearmonth in pmap:
ps = pmap[yearmonth]
monthSum = helper.to_string_price(sum([p['priceSum'] for p in ps]))
rlist.append({'year': yearmonth[:4], 'month': yearmonth[-2:], 'monthSum': monthSum, 'purchases': ps})
return {'purchaseslist': rlist}
def get_items_of_purchase(purchase_id):
database = db.get_db()
cur = database.execute("""SELECT * FROM item
WHERE purchaseid = %d;""" % purchase_id)
rows = cur.fetchall()
items = {}
idict = {'itemlist': [dict(row) for row in rows]}
return idict
def get_items_of_purchase(purchase_id):
database = db.get_db()
cur = database.execute("""SELECT * FROM item
WHERE purchaseid = %d;""" % purchase_id)
rows = cur.fetchall()
items = {}
idict = {'itemlist': [ dict(row) for row in rows ]}
return idict
def get_categoryid_for_itemname(itemname):
database = db.get_db()
cur = database.execute(
"""SELECT categoryid from mappings
WHERE mappings.item = ? """, [itemname])
rows = [dict(row) for row in cur.fetchall()]
if len(rows) < 1:
return None
return rows[0]['categoryid']
def get_categoryid_for_itemname(itemname):
database = db.get_db()
cur = database.execute("""SELECT categoryid from mappings
WHERE mappings.item = ? """,
[itemname])
rows = [ dict(row) for row in cur.fetchall() ]
if len(rows) < 1:
return None
return rows[0]['categoryid']
def get_store_by_name(storename):
database = db.get_db()
cur = database.execute(""" SELECT id
FROM store
WHERE store.name = ?
""", [storename])
rows = [dict(row) for row in cur.fetchall()]
if len(rows) < 1:
return None
return rows[0]['id']
def get_all_purchases():
database = db.get_db()
cur = database.execute("""SELECT p.id, p.timestamp, s.name AS storeName, SUM(i.price) AS priceSum
FROM purchase p, store s, item i
WHERE p.storeid = s.id AND i.purchaseid = p.id
GROUP BY i.purchaseid
ORDER BY p.timestamp;""")
rows = cur.fetchall()
pdict = {'purchaselist': [ dict(row) for row in rows ]}
return pdict
def get_categories_of_purchase(purchase_id):
database = db.get_db()
cur = database.execute("""SELECT c.name, SUM(i.price) AS sum
FROM purchase p, item i, category c
WHERE p.id = %d AND i.purchaseid = p.id
AND c.id = i.categoryid
GROUP BY c.id;""" % purchase_id)
rows = cur.fetchall()
categories = [dict(row) for row in rows]
return {'productCategories': [[c['name'], c['sum']] for c in categories]}
def get_categories_of_purchase(purchase_id):
database = db.get_db()
cur = database.execute("""SELECT c.name, SUM(i.price) AS sum
FROM purchase p, item i, category c
WHERE p.id = %d AND i.purchaseid = p.id
AND c.id = i.categoryid
GROUP BY c.id;""" % purchase_id)
rows = cur.fetchall()
categories = [dict(row) for row in rows]
return {'productCategories': [[c['name'], c['sum']] for c in categories]}
def get_all_purchases():
database = db.get_db()
cur = database.execute(
"""SELECT p.id, p.timestamp, s.name AS storeName, SUM(i.price) AS priceSum
FROM purchase p, store s, item i
WHERE p.storeid = s.id AND i.purchaseid = p.id
GROUP BY i.purchaseid
ORDER BY p.timestamp;""")
rows = cur.fetchall()
pdict = {'purchaselist': [dict(row) for row in rows]}
return pdict
def get_store_by_name(storename):
database = db.get_db()
cur = database.execute(
""" SELECT id
FROM store
WHERE store.name = ?
""", [storename])
rows = [dict(row) for row in cur.fetchall()]
if len(rows) < 1:
return None
return rows[0]['id']
def create_purchase(timestamp, storeid):
database = db.get_db()
cur = database.execute("""INSERT INTO purchase
(timestamp, storeid)
values
(?, ?);""",
[timestamp, storeid])
purchaseid = cur.lastrowid
database.commit()
app.logger.debug("Inserted purchase: {}".format(purchaseid))
return purchaseid
def create_purchase(timestamp, storeid):
database = db.get_db()
cur = database.execute(
"""INSERT INTO purchase
(timestamp, storeid)
values
(?, ?);""", [timestamp, storeid])
purchaseid = cur.lastrowid
database.commit()
app.logger.debug("Inserted purchase: {}".format(purchaseid))
return purchaseid
def create_item(name, price, quantity, purchaseid):
database = db.get_db()
categoryid = category.get_categoryid_for_itemname(name)
#app.logger.debug("Got Category {} for item {}".format(categoryid, name))
cur = database.execute("""INSERT INTO item
(name, price, quantity, purchaseid, categoryid)
values
(?, ?, ?, ?, ?);""",
[name, price, quantity, purchaseid, categoryid])
itemid = cur.lastrowid
database.commit()
return itemid
def create_item(name, price, quantity, purchaseid):
database = db.get_db()
categoryid = category.get_categoryid_for_itemname(name)
#app.logger.debug("Got Category {} for item {}".format(categoryid, name))
cur = database.execute(
"""INSERT INTO item
(name, price, quantity, purchaseid, categoryid)
values
(?, ?, ?, ?, ?);""",
[name, price, quantity, purchaseid, categoryid])
itemid = cur.lastrowid
database.commit()
return itemid
def get_categories_by_month(year, month):
database = db.get_db()
month_range = calendar.monthrange(year, month)
first = datetime.datetime(year=year,month=month,day=month_range[0]+1)
first_timestamp = calendar.timegm(first.utctimetuple())
last = datetime.datetime(year=year,month=month,day=month_range[1])
last_timestamp = calendar.timegm(last.utctimetuple())
cur = database.execute("""SELECT c.name, SUM(i.price) AS sum
FROM purchase p, item i, category c
WHERE i.purchaseid = p.id AND c.id = i.categoryid
AND %d < p.timestamp AND p.timestamp < %d
GROUP BY c.id;""" % (first_timestamp,last_timestamp) ) #% ("%%s", year_month))
rows = cur.fetchall()
categories = [dict(row) for row in rows]
return {'productCategories': [[c['name'], c['sum']] for c in categories]}
def get_categories_by_month(year, month):
database = db.get_db()
month_range = calendar.monthrange(year, month)
first = datetime.datetime(year=year, month=month, day=month_range[0] + 1)
first_timestamp = calendar.timegm(first.utctimetuple())
last = datetime.datetime(year=year, month=month, day=month_range[1])
last_timestamp = calendar.timegm(last.utctimetuple())
cur = database.execute(
"""SELECT c.name, SUM(i.price) AS sum
FROM purchase p, item i, category c
WHERE i.purchaseid = p.id AND c.id = i.categoryid
AND %d < p.timestamp AND p.timestamp < %d
GROUP BY c.id;""" %
(first_timestamp, last_timestamp)) #% ("%%s", year_month))
rows = cur.fetchall()
categories = [dict(row) for row in rows]
return {'productCategories': [[c['name'], c['sum']] for c in categories]}
def get_purchases_by_month():
database = db.get_db()
cur = database.execute(
"""SELECT p.id, p.timestamp, s.name AS storeName, SUM(i.price) AS priceSum
FROM purchase p, store s, item i
WHERE p.storeid = s.id AND i.purchaseid = p.id
GROUP BY i.purchaseid
ORDER BY p.timestamp DESC;""")
rows = cur.fetchall()
purchases = []
#pdict = {'purchaselist': [ dict(row) for row in rows ]}
for row in rows:
purchase_dict = dict(row)
purchase_dict['time'] = datetime.datetime.fromtimestamp(
purchase_dict['timestamp']).strftime('%d.%m.%Y %H:%M')
purchase_dict['yearmonth'] = datetime.datetime.fromtimestamp(
purchase_dict['timestamp']).strftime('%Y-%m')
purchase_dict['sum'] = helper.to_string_price(
purchase_dict['priceSum'])
del purchase_dict['timestamp']
purchases.append(purchase_dict)
pmap = {}
for p in purchases:
yearmonth = p['yearmonth']
if not yearmonth in pmap:
pmap[yearmonth] = []
pmap[yearmonth].append(p)
rlist = []
for yearmonth in pmap:
ps = pmap[yearmonth]
monthSum = helper.to_string_price(sum([p['priceSum'] for p in ps]))
rlist.append({
'year': yearmonth[:4],
'month': yearmonth[-2:],
'monthSum': monthSum,
'purchases': ps
})
return {'purchaseslist': rlist}
def get_categories():
database = db.get_db()
cur = database.execute("""SELECT * FROM category; """)
rows = [dict(row) for row in cur.fetchall()]
return rows
def get_mappings():
database = db.get_db()
cur = database.execute("""SELECT * FROM mappings; """)
rows = [dict(row) for row in cur.fetchall()]
return rows
def update_category_for_item(itemid, categoryid):
app.logger.debug("itemid: {}, categoryid: {}".format(itemid, categoryid))
database = db.get_db()
cur = database.execute("""UPDATE item SET categoryid=? WHERE id=?""",
[categoryid, itemid])
database.commit()
def get_mappings():
database = db.get_db()
cur = database.execute("""SELECT * FROM mappings; """)
rows = [dict(row) for row in cur.fetchall()]
return rows
def get_categories():
database = db.get_db()
cur = database.execute("""SELECT * FROM category; """)
rows = [dict(row) for row in cur.fetchall()]
return rows
def update_category_for_item(itemid, categoryid):
app.logger.debug("itemid: {}, categoryid: {}".format(itemid, categoryid))
database = db.get_db()
cur = database.execute("""UPDATE item SET categoryid=? WHERE id=?""",
[categoryid, itemid])
database.commit()
