def getOrders():
"""Get all orders and their information"""
sql.begin()
result = sql.cur.execute("SELECT oid, total, date, pid FROM orders")
res = result.fetchall()
sql.end()
return res
def keyUserList():
"""Get list of kid and pid in Tables"""
sql.begin()
result = sql.cur.execute("SELECT kid, pid FROM keys")
res = result.fetchall()
sql.end()
return res
def spendingsTotalStart():
"""The total spending from the system since start"""
sql.begin()
result = sql.cur.execute("""SELECT SUM(total) FROM orders""")
res = result.fetchone()
sql.end()
return res
def categoriesList():
"""Returns list of all categories :: [(a,b)(a,b)]"""
sql.begin()
result = sql.cur.execute('SELECT cid, name FROM categories')
res = result.fetchall()
sql.end()
return res
def newUser(
name, sid, password, hash, keyname
): # user will not be created under a new name if SID already exists due to UNIQUE CONSTRAINT
"""Create a new keyhash entry, new persons (pid) and link these in KPL"""
sql.begin()
hash = str(hash)
# insert card
sql.cur.execute("INSERT INTO keys (kid, keyname) VALUES(?,?)",
[hash, keyname])
sql.commit()
x = sql.cur.execute("SELECT * FROM persons WHERE sid=?", [sid])
res = x.fetchone()
if res == None:
# insert user
password = password.encode('utf-8')
hashed = bcrypt.hashpw(password, bcrypt.gensalt())
sql.cur.execute(
"INSERT INTO persons (name,sid,usertype,balance,password) VALUES(?,?,0,?,?)",
[name, sid, 0, hashed])
sql.commit()
pid = sql.lastId()
else:
pid = res[0]
sql.cur.execute("UPDATE keys SET pid=? WHERE kid=?", [pid, hash])
sql.commit()
sql.end()
def getUser(pid):
sql.begin()
result = sql.cur.execute(
"SELECT pid, sid, name, balance FROM persons where pid=?", [pid])
res = result.fetchone()
sql.end()
return res
def removeAdmin(pid):
"""Down someones usertype"""
if len(adminList()) > 1:
sql.begin()
sql.cur.execute("UPDATE persons SET usertype=0 WHERE pid=?", [pid])
sql.commit()
sql.end()
def getCategory(cid):
sql.begin()
result = sql.cur.execute('SELECT cid, name FROM categories WHERE cid=?',
[int(cid)])
res = result.fetchone()
sql.end()
return res
def spendingsTotal():
"""The total spending from the system since last RESET"""
sql.begin()
result = sql.cur.execute("""SELECT SUM(balance) FROM persons""")
res = result.fetchone()
sql.end()
return res
def addUserBalance(pid, add):
"""Sets one users balance to 0, useful for "system clean" after one invoice"""
sql.begin()
sql.cur.execute("UPDATE persons SET balance = balance - ? where pid=?",
[add, pid])
sql.commit()
sql.end()
def editUser(name, balance, sid, pid):
sql.begin()
sql.cur.execute(
"UPDATE persons SET name=?, sid=?, balance=? WHERE pid=?",
[str(name), str(sid), float(balance),
int(pid)])
sql.commit()
sql.end()
def getItems():
"""Gives an array of all items :: () -> [(a,b,c),(a,b,c)]"""
sql.begin()
result = sql.cur.execute(
'SELECT iid, item_name, stock, current_price, pic_url, cid FROM items')
res = result.fetchall()
sql.end()
return res
def adminList():
"""Get list of all current admins"""
sql.begin()
result = sql.cur.execute(
"SELECT pid,name,sid,balance FROM persons WHERE usertype=1")
res = result.fetchall()
sql.end()
return res
def getStats():
"""Return should be: most sold item, least sold item, cheapest item, average money spend, most items of most sold, number of different snacks"""
"""
First (iid,item_name,quant) is Most sold
Second (iid,item_name,quant) is least sold
Third (iid,item_name,current_price) is cheapest item
Fourth (average order price) is avg_order_price
Fifth (pid, username, quant) user who ate most of most sold item
Sixth (number of different items in system)
"""
res = []
sql.begin()
result = sql.cur.execute("""SELECT * FROM orderitems""")
z = (result.fetchone())
if z == None:
return None
result = sql.cur.execute(
"""SELECT items.iid, item_name, SUM(orderitems.quantity) as quant
FROM items, orderitems, orders
WHERE items.iid = orderitems.iid
AND orders.oid = orderitems.oid
GROUP by items.iid
order by quant desc
""")
res.append((result.fetchone()))
x = res[0][0]
result = sql.cur.execute(
"""SELECT items.iid, item_name, SUM(orderitems.quantity) as quant
FROM items, orderitems, orders
WHERE items.iid = orderitems.iid
AND orders.oid = orderitems.oid
GROUP by items.iid
order by quant asc
""")
res.append((result.fetchone()))
result = sql.cur.execute("""SELECT iid, item_name, MIN(current_price)
FROM items
""")
res.append((result.fetchone()))
result = sql.cur.execute("""SELECT AVG(total)
FROM orders
""")
res.append((result.fetchone()))
result = sql.cur.execute(
"""SELECT persons.pid, persons.name, SUM(orderitems.quantity) as quantitem
FROM persons, orders, orderitems
WHERE persons.pid = orders.pid
AND orderitems.iid = ?
AND orders.oid = orderitems.oid
GROUP BY persons.pid
order by quantitem desc
""", [x])
res.append((result.fetchone()))
result = sql.cur.execute("SELECT COUNT(iid) FROM items")
res.append((result.fetchone()))
sql.end()
return res
def getLinkedKeys(pid):
"""Gives info of keys for user:: (int pid) --> (pid, name, keyname)"""
sql.begin()
results = sql.cur.execute(
"SELECT persons.pid, name, keyname FROM keys, persons WHERE keys.pid = persons.pid AND persons.pid=?",
[pid])
res = results.fetchall()
sql.end()
return res
def getFilterdItems(cid):
"""Gives an array of all items based on Category:: int(f) -> [(a,b,c,d,e,f),(a,b,c,d,e,f)]"""
sql.begin()
result = sql.cur.execute(
'SELECT iid, item_name, stock, current_price, pic_url, cid FROM items WHERE cid=?',
[int(cid)])
res = result.fetchall()
sql.end()
return res
def getItem(iid):
"""Gives info of One item:: int(a) -> (a, b, c, d, e, f)"""
sql.begin()
result = sql.cur.execute(
'SELECT iid, item_name, stock, current_price, pic_url, cid FROM items WHERE iid=?',
[int(iid)])
res = result.fetchone()
sql.end()
return res
def newItem(name, stock, price, image, cid):
"""Insert new item, iid should be created automatically by the DB::(name, stock, price, image, cid)"""
sql.begin()
sql.cur.execute(
'INSERT INTO items (item_name, stock, current_price, pic_url, cid) VALUES (?, ?, ?, ?, ?)',
[str(name), int(stock), price,
str(image), int(cid)])
sql.commit()
sql.end()
def userList():
"""Fetch list of /normal/ users"""
sql.begin()
result = sql.cur.execute(
"SELECT pid, name, sid, balance, usertype FROM persons WHERE usertype=0"
)
res = result.fetchall()
sql.end()
return res
def spendingsByUser():
"""Get list of spendings per user for last time since RESET"""
sql.begin()
result = sql.cur.execute("""SELECT persons.pid, persons.name,persons.balance
FROM persons
""")
res = result.fetchall()
sql.end()
return res
def getSQLDump():
sql.begin()
filename = 'dump.sql'
path = 'template_static/sql/{}'.format(filename)
sql_out = open(path, 'w')
with sql_out:
for line in sql.con.iterdump():
sql_out.write('%s\n' % line)
sql.end()
emit('sql', {'data': filename})
def getUsers():
"""Mainly debug for quick overview of data to test allowed inputs for other queries"""
sql.begin()
result = sql.cur.execute(
'''SELECT kid, persons.pid, persons.name,persons.sid,persons.balance, usertype
FROM persons, keys
WHERE keys.pid = persons.pid ''')
res = result.fetchall()
sql.end()
return res
def CheckInsOuts(date) :
"""Check what has gone in (stock) and what has gone out (from transactions since x date)"""
sql.begin()
result = sql.cur.execute("""SELECT items.stock, SUM(transactions.quantity)
FROM items, orderitems
WHERE items.iid = orderitems.iid
AND date >= datetime(?)
GROUP BY items.iid"""[date])
res = result.fetchone()
sql.end()
return res
def removeOrder(oid):
"""Removes Order :: (int)"""
sql.begin()
order = sql.cur.execute("SELECT total, pid FROM orders WHERE oid=?", [oid])
order = order.fetchone()
addUserBalance(order[1], order[0])
sql.begin()
sql.cur.execute("DELETE FROM orders WHERE oid=?", [oid])
sql.commit()
sql.end()
removeOrderItems(oid)
def getUserOrders(pid):
"""Get orders for pid:: (int)--> [(pid,name,oid,total,date)]"""
sql.begin()
result = sql.cur.execute(
"""SELECT persons.pid, name, oid, total, date
FROM persons, orders
WHERE orders.pid = persons.pid and persons.pid=?""",
[pid])
res = result.fetchall()
sql.end()
return res
def spendingsByUserTotal():
"""Spendings by ALL users in system since start of system"""
sql.begin()
result = sql.cur.execute("""SELECT DISTINCT orders.pid, persons.name, SUM(orders.total)
FROM orders, persons
where persons.pid = orders.pid
GROUP BY orders.pid
""")
res = result.fetchall()
sql.end()
return res
def UserCheck(kid):
"""Select user data from keyhash recieved"""
sql.begin()
result = sql.cur.execute(
"""SELECT persons.name,persons.sid,persons.balance
from persons, keys
WHERE kid = ?
AND keys.pid = persons.pid""", [kid])
res = result.fetchall()
sql.end()
return res
def getOrderItems(oid):
"""Get all items for an oid"""
sql.begin()
result = sql.cur.execute(
"""SELECT oid, orderitems.iid,items.item_name, quantity, price
FROM items, orderitems
WHERE orderitems.oid = ?
and items.iid = orderitems.iid""", [oid])
res = result.fetchall()
sql.end()
return res
def getUserId(hash):
sql.begin()
sql.cur.execute("SELECT pid FROM keys WHERE kid=?", [str(hash)])
pid = sql.cur.fetchone()
if pid == None:
sql.end()
return None
else:
sql.cur.execute("SELECT * FROM persons WHERE pid=?", [pid[0]])
person = sql.cur.fetchone()
sql.end()
return person
def loginUser(sid, password):
sql.begin()
res = sql.cur.execute("SELECT * FROM persons WHERE sid=?", [sid])
person = res.fetchone()
sql.end()
if person != None:
password = password.encode('utf-8')
if bcrypt.hashpw(password, person[5]) == person[5]:
return person
else:
return None
else:
return None
